package implicits

import java.io.{File, FileInputStream}
import java.text.SimpleDateFormat
import org.apache.commons.io.FileUtils
import org.apache.commons.lang3.StringUtils
import org.apache.poi.ss.usermodel.{Cell, DateUtil}
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import scala.collection.JavaConverters._
import implicits.Implicits._
import shared.Pojo.CellData
import com.softwaremill.quicklens._

/**
 * Created by Administrator on 2019/11/11
 */

trait MyXlxsTool {

  implicit class MyXlxsFile(file: File) {

    def xlsxLines(sheetIndex: Int = 0) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val lines = (0 to xssfSheet.getLastRowNum).toList.filter { i =>
        val xssfRow = xssfSheet.getRow(i)
        xssfRow != null
      }.map { i =>
        val xssfRow = xssfSheet.getRow(i)
        val firstRow = xssfSheet.getRow(0)
        (0 until firstRow.getLastCellNum).map { j =>
          val cell = xssfRow.getCell(j)
          val value = if (cell == null) {
            ""
          } else {
            cell.getCellType match {
              case Cell.CELL_TYPE_STRING =>
                cell.getStringCellValue
              case Cell.CELL_TYPE_NUMERIC =>
                if (DateUtil.isCellDateFormatted(cell)) {
                  val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
                  dateFormat.format(cell.getDateCellValue)
                } else {
                  val doubleValue = cell.getNumericCellValue
                  if (doubleValue == doubleValue.toInt) {
                    doubleValue.toInt.toString
                  } else doubleValue.toString
                }
              case _ =>
                ""
            }
          }
          value.trim
        }.toList
      }
      xssfWorkbook.close()
      lines.filter(x => x.exists(y => StringUtils.isNotBlank(y)))
    }

    def xlsxInfoLines(sheetIndex: Int = 0) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val lines = (0 to xssfSheet.getLastRowNum).toList.filter { i =>
        val xssfRow = xssfSheet.getRow(i)
        xssfRow != null
      }.map { i =>
        val xssfRow = xssfSheet.getRow(i)
        val firstRow = xssfSheet.getRow(0)
        (0 until firstRow.getLastCellNum).map { j =>
          val cell = xssfRow.getCell(j)
          val value = if (cell == null) {
            ""
          } else {
            cell.getCellType match {
              case Cell.CELL_TYPE_STRING =>
                cell.getStringCellValue
              case Cell.CELL_TYPE_NUMERIC =>
                if (DateUtil.isCellDateFormatted(cell)) {
                  val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
                  dateFormat.format(cell.getDateCellValue)
                } else {
                  val doubleValue = cell.getNumericCellValue
                  if (doubleValue == doubleValue.toInt) {
                    doubleValue.toInt.toString
                  } else doubleValue.toString
                }
              case _ =>
                ""
            }
          }
          val rowspan =if(cell==null){
            0
          }else{
            val mergedRegionOp = xssfSheet.getMergedRegions.asScala.toList.find { mergedRegion =>
              mergedRegion.isInRange(cell.getRowIndex, cell.getColumnIndex)
            }
            mergedRegionOp.map { mergedRegion =>
              if (mergedRegion.getFirstRow == cell.getRowIndex && mergedRegion.getFirstColumn == cell.getColumnIndex) {
                mergedRegion.getLastRow - mergedRegion.getFirstRow + 1
              } else 0
            }.getOrElse(1)
          }
          CellData(value = value.trim, rowspan = rowspan)
        }.toList
      }
      xssfWorkbook.close()
      lines
    }

    def xlsx2Txt(txtFile: File) = {
      val lines = file.xlsxLines()
      lines.toFile(txtFile)
    }

    def info = {
      val lines = file.xlsxInfoLines()
      val columnNames = lines.head.map(_.value)
      val cNames = lines.head.zipWithIndex.map { case (v, i) =>
        s"c${i}"
      }
      val map = cNames.zip(columnNames).toSeqMap
      val array = lines.drop(1).map { tmpCellDatas =>
        val cellDatas = tmpCellDatas.map { cellData =>
          cellData.modify(_.value).using(_.str.trimQuote)
        }.padTo(cNames.size, CellData(value = "", rowspan = 1))
        cNames.zip(cellDatas).toMap
      }
      (map, array)
    }


  }
}


